package org.example.deep.service.impl;

import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.net.URLEncodeUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.cell.CellUtil;
import com.alibaba.fastjson.JSON;
import org.example.deep.dao.ElecAnalyseRecordRepository;
import org.example.deep.dao.ElecDeepFileRepository;
import org.example.deep.entity.ElecAnalyseRecord;
import org.example.deep.entity.ElecDeepFile;
import org.example.deep.utils.json.ResultJson;
import org.example.deep.service.IElecDeepService;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xddf.usermodel.chart.XDDFChartData;
import org.apache.poi.xddf.usermodel.chart.XDDFDataSource;
import org.apache.poi.xddf.usermodel.chart.XDDFDataSourcesFactory;
import org.apache.poi.xddf.usermodel.chart.XDDFNumericalDataSource;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xwpf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * @author jianxin.wei
 * @description
 * @date 2024/9/10 10:40
 */
@Slf4j
@Service
public class IElecDeepServiceImpl implements IElecDeepService {
    private List<Sheet> sheets = null;

    @Autowired
    private ElecDeepFileRepository fileRepository;
    @Resource
    private ElecAnalyseRecordRepository recordRepository;

    @Override
    public ResultJson upload(MultipartFile file) {
//        导入模板

        log.info("深谷电价数据文件上传");
        ResultJson resultJson = new ResultJson();
        String curYear = null;
        String curMonth = null;
        String curDay = null;
        String pastYear = null;
        String pastMonth = null;
        String pastDay = null;

        try {
            if (file == null) {
                return resultJson.fail("文件数量不符合要求");
            }
            String filename = file.getOriginalFilename();
            String regex = "(\\d{4})年(\\d{2})月(\\d{2})日深谷电价周报数据\\.(xlsx|xls)";
            if (regex.matches(filename)) {
                return resultJson.fail("文件名称不符合要求");
            }
            ExcelReader reader = ExcelUtil.getReader(file.getInputStream());

            // 替换的数据
            List<List<String>> textList = new ArrayList<>();
            List<Queue<String>> tableList = new ArrayList<>();
            List<String> chartList = new ArrayList<>();

            // 找时间
            Pattern pattern = Pattern.compile(regex);
            Matcher matcher = pattern.matcher(filename);
            String curDate = null;
            String pastDate = null;
            if (matcher.find()) {
                curYear = matcher.group(1);
                curMonth = matcher.group(2);
                curDay = matcher.group(3);
                DateTime cur = DateUtil.parse(curYear + "-" + curMonth + "-" + curDay, "yyyy-MM" +
                        "-dd");
                DateTime past = DateUtil.offsetDay(cur, -7);

                pastYear = DateUtil.format(past, "yyyy");
                pastMonth = DateUtil.format(past, "MM");
                pastDay = DateUtil.format(past, "dd");
                curDate = curYear + "年" + curMonth + "月" + curDay + "日";
                pastDate = pastYear + "年" + pastMonth + "月" + pastDay + "日";
            }
            System.out.println(pastMonth);


            sheets = reader.getSheets();



            // 获取填充数据
            int dataNum = getFillData(textList, tableList, chartList, curDate, pastDate);
            // 记录数据数
            long seq = recordRepository.findSeq();
            ElecAnalyseRecord record = new ElecAnalyseRecord();
            record.setId(seq);
            record.setDataType(7);
            record.setDataNum(dataNum);
            record.setCreateDate(LocalDateTime.now());
            recordRepository.save(record);


            // 替换
            execute(textList, tableList, chartList ,curDate);

            long seq1 = recordRepository.findSeq();
            ElecAnalyseRecord record1 = new ElecAnalyseRecord();
            record1.setId(seq1);
            record1.setDataType(8);
            record1.setDataNum(1);
            record1.setCreateDate(LocalDateTime.now());
            recordRepository.save(record1);

            String fileName = curDate + "深谷电价周报";
            return resultJson.success(fileName);


        } catch (Exception e) {
            log.info("深谷分析模型文件上传异常", e);
            return resultJson.fail("深谷电价周报生成失败");
        }
    }

    /**
     * @param textList,tableList,chartList
     * @return void
     * @description 替换
     * @author jianxin.wei
     * @date 2024/9/13 09:16
     */
    private void execute(List<List<String>> textList, List<Queue<String>> tableList,
                         List<String> chartList , String curDate) throws IOException {
        ElecDeepFile elecDeepFile = fileRepository.findTemplate(1);
        byte[] fileData = elecDeepFile.getFileData();
//        byte[] fileData = FileUtil.readBytes("E:\\shark\\需求文档\\9.10 - 报表\\周报模板.docx");
        log.info("读取深谷电价周报模板内容：");
        XWPFDocument xwpfDocument = new XWPFDocument(new ByteArrayInputStream(fileData));
        List<XWPFParagraph> paragraphs = xwpfDocument.getParagraphs();
        List<XWPFParagraph> paragraphList = new ArrayList<>();
        for (int i = 0; i < paragraphs.size(); i++) {
            String text = paragraphs.get(i).getText();
            if (text.contains("{}") | text.contains("{_}")) {
                log.info("深谷电价周报模板==>需要填充第" + i + "段内容----->" + text);
                paragraphList.add(paragraphs.get(i));
            }
        }
        // 替换文本内容
        fillParagraphs(paragraphList, textList);


        // 填充表格
        List<XWPFTable> tables = xwpfDocument.getTables();
        fillTables(tables, tableList);

        // 替换图标内容
        List<XWPFChart> charts = xwpfDocument.getCharts();
        fillCharts(charts, chartList);


        try (ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {

            xwpfDocument.write(outputStream);
            outputStream.flush();
            byte[] bytes = outputStream.toByteArray();
//            FileUtil.writeBytes(bytes, "E:\\shark\\需求文档\\9.10 - 报表\\深谷电价周报.docx");
//             从数据库获取序列号或文件ID
            long seq = fileRepository.findSeq();
//             尝试根据文件名和类型从数据库中查找已存在的文件
            String fileName = curDate + "深谷电价周报";
            ElecDeepFile file = fileRepository.findByFileName(fileName, 3);
//             如果文件已存在，更新文件数据和创建日期
            if (file != null) {
                file.setFileData(bytes);
                file.setCreateDate(LocalDateTime.now());
                fileRepository.save(file);
            } else {
                // 如果文件不存在，创建新文件，设置属性并存储
                file = new ElecDeepFile();
                file.setId(seq);
                file.setFileName(fileName);
                file.setSuffix("doc");
                file.setFileType(3);
                file.setFileData(bytes);
                file.setCreateDate(LocalDateTime.now());
                fileRepository.save(file);
            }

        }
    }

    private void fillCharts(List<XWPFChart> charts, List<String> chartList) {
        try {
            XWPFChart chart = charts.get(0);
            XSSFWorkbook workbook = chart.getWorkbook();
            XSSFSheet sheet = workbook.getSheetAt(0);

            sheet.getRow(1).getCell(0).setCellValue("代理购电用户");
            sheet.getRow(1).getCell(1).setCellValue(Double.parseDouble(chartList.get(1)));
            sheet.getRow(2).getCell(0).setCellValue("市场化用户");
            sheet.getRow(2).getCell(1).setCellValue(Double.parseDouble(chartList.get(0)));

            XDDFChartData chartData2 = chart.getChartSeries().get(0);
            XDDFChartData.Series s2 = chartData2.getSeries().get(0);
            XDDFDataSource cat2 = XDDFDataSourcesFactory.fromStringCellRange(sheet,
                    new CellRangeAddress(1, 2, 0, 0));
            XDDFNumericalDataSource val2 = XDDFDataSourcesFactory.fromNumericCellRange(sheet,
                    new CellRangeAddress(1, 2, 1, 1));
            s2.replaceData(cat2, val2);
            chart.plot(chartData2);
        } catch (IOException | InvalidFormatException e) {
            throw new RuntimeException(e);
        }
    }

    private void fillTables(List<XWPFTable> tables, List<Queue<String>> tableList) {
        for (int k = 0; k < tables.size(); k++) {
            XWPFTable xwpfTable = tables.get(k);
            List<XWPFTableRow> rows = xwpfTable.getRows();
            Queue<String> queue = tableList.get(k);
            for (int i = 0; i < rows.size(); i++) {
                for (int j = 0; j < rows.get(0).getTableICells().size(); j++) {
                    XWPFRun run =
                            rows.get(i).getCell(j).getParagraphs().get(0).getRuns().get(0);
                    if ("{}".equals(run.getText(0))) {
                        run.setText(queue.poll(), 0);
                    }
                }
            }
//        查看替换后的表格
           /* for (int i = 0; i < rows.size(); i++) {
                for (int j = 0; j < rows.get(0).getTableICells().size(); j++) {
                    String text =
                            rows.get(i).getCell(j).getParagraphs().get(0).getRuns().get(0)
                            .getText(0);
                    System.out.print("  " + text);

                }
                System.out.println();
            }*/
        }


    }

    private void fillParagraphs
            (List<XWPFParagraph> paragraphList, List<List<String>> textList) {
        for (int j = 0; j < paragraphList.size(); j++) {
            XWPFParagraph paragraph = paragraphList.get(j);
            List<XWPFRun> runs = paragraph.getRuns();
            List<String> list =  textList.get(j);
            int n = 0;
            for (int i = 0; i < runs.size(); i++) {
                if (n >= list.size()) {
                    break;
                }
                XWPFRun run = runs.get(i);
                String text = run.getText(0);
                if ("{}".equals(text) || "{_}".equals(text)) {
                    String newText = list.get(n);
                    if ("{_}".equals(text)) {
                        run.setBold(true);
                    }
                    run.setText(newText, 0);
                    n++;
                }
            }

            /*// 打印替换后的段落内容
            StringBuilder sb = new StringBuilder();
            for (XWPFRun run : paragraph.getRuns()) {
                sb.append(run.getText(0));
            }
            System.out.println("替换后的段落 " + (j + 1) + ": " + sb.toString());*/
        }
    }


    private String format(Double v) {
        return String.format("%.2f", v);
    }

    private Double getData(int sheet, int row, int cell) {
        return Double.parseDouble(CellUtil.getCellValue(sheets.get(sheet).getRow(row).getCell(cell)).toString());
    }

    private int getFillData
            (List<List<String>> textList, List<Queue<String>> tableList,
             List<String> chartList, String curDate,
             String pastDate) {

        int dataNum = 0;

        Queue<String> table1 = new ArrayDeque<>();
        Queue<String> table2 = new ArrayDeque<>();
        Queue<String> table3 = new ArrayDeque<>();
        // 第六段内容开始需要填充内容
        List<String> list6 = new ArrayList<>();
        List<String> list7 = new ArrayList<>();
        List<String> list14 = new ArrayList<>();
        List<String> list16 = new ArrayList<>();
        List<String> list21 = new ArrayList<>();
        List<String> list22 = new ArrayList<>();
        List<String> list23 = new ArrayList<>();
        List<String> list24 = new ArrayList<>();

        Double curTotal = getData(0, 2, 1) / 10;
        Double pastTotal = getData(0, 2, 2) / 10;
        Double huanbiTotal = getData(0, 2, 3) * 100;
        Double curDeep = getData(0, 3, 1) / 10;
        Double pastDeep = getData(0, 3, 2) / 10;
        Double huanbiDeep = getData(0, 3, 3) * 100;

        list6.add(curDate);
        list6.add(format(getData(0, 6, 1) / 10000));
        list6.add(format(curTotal));
        String huanbiTotalStr = format(huanbiTotal);
        list6.add(huanbiTotalStr.contains("-") ? "减少" + huanbiTotalStr.substring(1) :
                "增加" + huanbiTotalStr.substring(1));
        list6.add(format(curDeep));
        String huanbiDeepStr = format(huanbiDeep);
        list6.add(huanbiDeepStr.contains("-") ? "减少" + huanbiDeepStr.substring(1) :
                "增加" + huanbiDeepStr.substring(1));
        textList.add(list6);

        table1.add(curDate);
        table1.add(pastDate);
        table1.add(format(curTotal));
        table1.add(format(pastTotal));
        table1.add(format(huanbiTotal));
        table1.add(format(curDeep));
        table1.add(format(pastDeep));
        table1.add(format(huanbiDeep));
        table1.add(format(curDeep / curTotal * 100));
        table1.add(format(pastDeep / pastTotal * 100));
        table1.add(format((curDeep / curTotal * 100 / pastDeep / pastTotal * 100) - 1));
        tableList.add(table1);


        list7.add(curDate);
        list7.add(format((getData(0, 3, 1) / getData(0, 2, 1)) * 100));
        list7.add(format((curDeep / curTotal * 100 / pastDeep / pastTotal * 100) - 1));

        textList.add(list7);


        // 第二产业深谷
        Double curTwo = getData(1, 2, 1) / 10000;
        // 第三产业
        Double curThree = getData(1, 3, 1) / 10000;
        // 合计
        Double curTotal2 = getData(1, 4, 1) / 10000;

        // 上周 第二产业
        Double pastTwo = getData(1, 2, 4) / 10000;
        // 第三产业
        Double pastThree = getData(1, 3, 4) / 10000;
        // 合计
        Double pastTotal2 = getData(1, 4, 4) / 10000;

        Double huanbiTwo = (curTwo / pastTwo - 1) * 100;
        Double huanbiThree = (curThree / pastThree - 1) * 100;
        Double huanbiTotal2 = (curTotal2 / pastTotal2 - 1) * 100;

        // 整日占比
        Double ratioTwo = getData(1, 2, 3) * 100;
        Double ratioThree = getData(1, 3, 3) * 100;
        Double ratioTotal = getData(1, 4, 3) * 100;

        // 填谷率
        Double deepRatioTwo = (getData(1, 2, 3) / getData(1, 2, 6) - 1) * 100;
        Double deepRatioThree = (getData(1, 3, 3) / getData(1, 3, 6) - 1) * 100;
        Double deepRatioTotal = (getData(1, 4, 3) / getData(1, 4, 6) - 1) * 100;

        table2.add(format(curTwo));
        table2.add(format(huanbiTwo));
        table2.add(format(ratioTwo));
        table2.add(format(deepRatioTwo));
        table2.add(format(curThree));
        table2.add(format(huanbiThree));
        table2.add(format(ratioThree));
        table2.add(format(deepRatioThree));
        table2.add(format(curTotal2));
        table2.add(format(huanbiTotal2));
        table2.add(format(ratioTotal));
        table2.add(format(deepRatioTotal));

        tableList.add(table2);


        list14.add(curDate);
        list14.add(format(curTwo));
        String huanbiTwoStr = format(huanbiTwo);
        list14.add(huanbiTwoStr.contains("-") ? "减少" + huanbiTwoStr.substring(1) :
                "增长");
        list14.add(format(deepRatioTwo));
        list14.add(huanbiTwo > 0 ? "增加" : "下滑");
        list14.add(format(curThree));
        String huanbiThreeStr = format(huanbiThree);
        list14.add(huanbiThreeStr.contains("-") ? "减少" + huanbiThreeStr.substring(1) :
                "增长" + huanbiThreeStr);
        list14.add(format(deepRatioThree));
        list14.add(huanbiThree > 0 ? "增加" : "下滑");

        textList.add(list14);

        String maxName = "";
        Double max = 0.0;
        Double huanbi16 = 0.0;
        for (int i = 2; i < 12; i++) {
            Double num = getData(2, i, 1);
            if (max < num) {
                max = num / 10000;
                maxName =
                        String.valueOf(CellUtil.getCellValue(sheets.get(2).getRow(i).getCell(0)));
                huanbi16 = getData(2, i, 7) * 100;
            }
        }
        list16.add(curDate);
        list16.add(maxName.substring(2));
        list16.add(format(max));
        list16.add(format(huanbi16));
        textList.add(list16);


        // 市场化用户\代理购电用户
        Double schUser = getData(3, 1, 1);
        Double dlgdUser = getData(3, 2, 1);
        // 计算总和
        Double total = schUser + dlgdUser;
        // 计算各自的占比
        double schUserRatio = schUser * 100 / total;
        double dlgdUserRatio = dlgdUser * 100 / total;

        chartList.add(format(schUserRatio));
        chartList.add(format(dlgdUserRatio));

        list21.add(curDate);
        list21.add(format(dlgdUserRatio));
        list21.add(format(schUserRatio));
        textList.add(list21);

        list22.add(curDate);
        textList.add(list22);


        list23.add(curDate);
        textList.add(list23);


        String table3Date = curDate.substring(6);
        table3.add(table3Date);
        Double powerTotal = 0.0;
        // 收入
        Double incomeTotal = 0.0;
        for (int i = 2; i < 7; i++) {
            for (int j = 1; j < 5; j++) {
                Double num = getData(4, i, j);

                if (j == 1 || j == 4) {
                    num = num / 10000;
                    if (j == 1) {
                        powerTotal += num;
                    } else if (j == 4) {
                        incomeTotal += num;
                    }
                    table3.add(format(num));
                } else {
                    table3.add(String.valueOf(num));
                }
            }
        }
        table3.add(format(powerTotal));
        table3.add(format(incomeTotal));
        tableList.add(table3);

        list24.add(format(incomeTotal));
        textList.add(list24);

        for (List<String> list : textList) {
            dataNum += list.size();
        }
        for (Queue<String> list : tableList) {
            dataNum += list.size();
        }
        dataNum+=chartList.size();



        return dataNum;
    }

    @Override
    public void download(String fileName, HttpServletResponse response) {
        ResultJson resultJson = new ResultJson();
        try (ServletOutputStream outputStream = response.getOutputStream()) {
            ElecDeepFile file = fileRepository.findByFileName(fileName, 3);
            if (file == null) {
                response.setHeader("Content-Type", "application/json");
                outputStream.write(JSON.toJSONString(resultJson.fail("文件不存在")).getBytes(StandardCharsets.UTF_8));
                return;
            }
            response.setContentType("application/octet-stream");
            response.setHeader("Content-Type", "application/octet-stream");
            response.setHeader("Content-Disposition", "attachment;fileName=" + URLEncodeUtil.encode(fileName + "." + file.getSuffix(), StandardCharsets.UTF_8));
            outputStream.write(file.getFileData());
            outputStream.flush();
        } catch (Exception e) {
            response.setHeader("Content-Type", "application/json");
            log.info("深谷电价周报下载失败", e);
        }
    }

    @Override
    public void uploadTemplate() {
        String filePath = "E:\\shark\\需求文档\\9.10 - 报表\\周报数据模板.xlsx";
        try {
            byte[] fileData = Files.readAllBytes(Paths.get(filePath));
            ElecDeepFile elecDeepFile = new ElecDeepFile();
            long seq = fileRepository.findSeq();
            elecDeepFile.setId(seq);
            elecDeepFile.setFileName("xxxx年xx月xx日深谷电价周报数据");
            elecDeepFile.setFileData(fileData);
            elecDeepFile.setFileType(0);
            elecDeepFile.setSuffix("xlsx");
            elecDeepFile.setCreateDate(DateUtil.date().toLocalDateTime());
            fileRepository.save(elecDeepFile);
        } catch (IOException e) {
            e.printStackTrace();
        }
        filePath = "E:\\shark\\需求文档\\9.10 - 报表\\周报替换模板.docx";
        try {
            byte[] fileData = Files.readAllBytes(Paths.get(filePath));
            ElecDeepFile elecDeepFile = new ElecDeepFile();
            long seq = fileRepository.findSeq();
            elecDeepFile.setId(seq);
            elecDeepFile.setFileName("深谷电价周报替换模板");
            elecDeepFile.setFileData(fileData);
            elecDeepFile.setFileType(1);
            elecDeepFile.setSuffix("docx");
            elecDeepFile.setCreateDate(DateUtil.date().toLocalDateTime());
            fileRepository.save(elecDeepFile);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    @Override
    public ResultJson history() {
        log.info("历史深谷电价周报");
        ResultJson resultJson = new ResultJson();
        try {
            List<ElecDeepFile> files = fileRepository.findAllFile(3);
            List<Map<String, String>> collect = files.stream().map(file -> {
                Map<String, String> map = new HashMap<>();
                map.put("fileName", file.getFileName() + "." + file.getSuffix());
                map.put("createDate", file.getCreateDate().format(DateTimeFormatter.ofPattern("yyyy年MM月dd日 HH:mm:ss")));
                return map;
            }).collect(Collectors.toList());
            return resultJson.success(collect);
        } catch (Exception e) {
            log.info("历史深谷电价周报查询异常", e);
            return resultJson.fail("历史深谷电价周报查询失败");
        }
    }

    @Override
    public void downloadTemplate( HttpServletResponse response) {
        ResultJson resultJson = new ResultJson();
        try (ServletOutputStream outputStream = response.getOutputStream()) {

            String fileName = "xxxx年xx月xx日深谷电价周报数据";


            ElecDeepFile file = fileRepository.findByFileName(fileName, 0);
            if (file == null) {
                response.setHeader("Content-Type", "application/json");
                outputStream.write(JSON.toJSONString(resultJson.fail("文件不存在")).getBytes(StandardCharsets.UTF_8));
                return;
            }
            response.setContentType("application/octet-stream");
            response.setHeader("Content-Type", "application/octet-stream");
            response.setHeader("Content-Disposition", "attachment;fileName=" + URLEncodeUtil.encode(fileName + "." + file.getSuffix(), StandardCharsets.UTF_8));
            outputStream.write(file.getFileData());
            outputStream.flush();
        } catch (Exception e) {
            response.setHeader("Content-Type", "application/json");
            log.info("深谷电价周报模板下载失败", e);
        }
    }

    /*@Override
    public ResultJson total() {
        ResultJson resultJson = new ResultJson();
        try {
            Map<String, Integer> map = new HashMap<>();
            List<Map<String, Integer>> mapList = recordRepository.countByType();
            if (mapList.isEmpty()) {
                map.put("dataNum", 0);
                map.put("reportNum", 0);
            } else {
                int dataNum = mapList.stream().filter(m -> "1".equals(m.get("DATA_TYPE").toString())).mapToInt(m -> m.get("NUM")).sum();
                int reportNum = mapList.stream().filter(m -> "2".equals(m.get("DATA_TYPE").toString())).mapToInt(m -> m.get("NUM")).sum();
                map.put("dataNum", dataNum);
                map.put("reportNum", reportNum);
            }
            return resultJson.success(map);
        } catch (Exception e) {
            log.info("获取汇总统计数据异常", e);
            return resultJson.fail("获取汇总统计数据失败");
        }
    }*/

}
